package cn.backpackerxl.easyJDBC.util;

import cn.backpackerxl.easyJDBC.annotation.*;
import cn.backpackerxl.easyJDBC.annotation.Character;
import cn.backpackerxl.easyJDBC.exception.FieldException;
import cn.backpackerxl.easyJDBC.exception.NoPrimaryKey;
import cn.backpackerxl.easyJDBC.exception.NoSqlException;
import cn.backpackerxl.easyJDBC.exception.NoTableException;
import cn.backpackerxl.easyJDBC.main.Executor;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.*;

import static cn.backpackerxl.easyJDBC.util.StringUtil.removeHump;
import static cn.backpackerxl.easyJDBC.util.MD5Passwd.code;
import static cn.backpackerxl.easyJDBC.util.MySQL.*;
import static cn.backpackerxl.easyJDBC.util.MySQL.lts;

/**
 * sql工厂
 *
 * @author: backpackerxl
 * @create: 2021/12/24
 * @filename: SetSql
 **/
public final class SqlFactory {

    private SqlFactory() {
    }

    /**
     * 拼接保存数据和修改数据的sql语句.
     *
     * @param table  表名
     * @param fields 拼接的问号个数
     * @return sql语句
     */
    public static String joinSqlSave(String table, Field[] fields) {
        String sql = String.format("%s %s %s", MySQL.INSERT, MySQL.INTO, table);
        StringBuilder fi = new StringBuilder("(");
        StringBuilder value = new StringBuilder(String.format("%s(", MySQL.VALUES));
        boolean isId = false;
        for (Field field : fields) {
            if (field.isAnnotationPresent(IgnoreField.class)) {
                continue;
            } else {
                fi.append(StringUtil.removeHump(field.getName())).append(",");
            }
            if (field.isAnnotationPresent(Id.class)) {
                value.append(",?");
                isId = true;
            } else {
                value.append(",?");
            }
        }
        String columns = fi.toString();
        columns = columns.substring(0, columns.length() - 1);
        if (isId) {
            return String.format("%s %s) %s)", sql, columns, value).replace("(,", "(");
        } else {
            throw new NoPrimaryKey("the primary key is undefined");
        }
    }

    /**
     * 拼接修改的sql语句
     *
     * @param value  表名
     * @param fields 字段列表
     * @return sql语句
     */
    public static String joinSqlUpdate(String value, Field[] fields) {
        String sql = String.format("%s %s %s", MySQL.UPDATE, value, MySQL.SET);
        StringBuilder sqlField = new StringBuilder();
        String primaryKey = null;
        for (Field field : fields) {
            if (field.isAnnotationPresent(IgnoreField.class)) {
                continue;
            }
            if (field.isAnnotationPresent(Id.class)) {
                primaryKey = removeHump(field.getName());
            } else {
                sqlField.append(removeHump(field.getName())).append("=?,");
            }
        }
        String sqlSet = sqlField.toString();
        sqlSet = sqlSet.substring(0, sqlSet.length() - 1);
        if (primaryKey != null) {
            return String.format("%s %s %s %s=?", sql, sqlSet, MySQL.WHERE, primaryKey);
        } else {
            throw new NoPrimaryKey("the primary key is undefined");
        }
    }

    /**
     * 获得表名
     *
     * @param obj 实体
     * @return 表名
     */
    public static String getTableName(Object obj) {
        Class<?> clazz = obj.getClass();
        return getName(clazz);
    }

    /**
     * 获得表名
     *
     * @param clazz 实体
     * @return 表名
     */
    public static String getTableName(Class<?> clazz) {
        return getName(clazz);
    }

    /**
     * 获取名字
     *
     * @param clazz
     * @return
     */
    private static String getName(Class<?> clazz) {
        if (clazz.isAnnotationPresent(Table.class)) {
            String value = clazz.getAnnotation(Table.class).value();
            if (value.equals("t_&")) {
                String[] split = clazz.getName().split("[.]");
                value = value.replace("&", split[split.length - 1].toLowerCase(Locale.ROOT));
            }
            return value;
        } else {
            throw new NoTableException("the entity is not a table, because no @Table");
        }
    }

    /**
     * 获取主键
     *
     * @param obj
     * @return
     */
    public static String getTableId(Object obj) {
        Class<?> clazz = obj.getClass();
        String name = null;
        for (Field field : clazz.getDeclaredFields()) {
            if (field.isAnnotationPresent(Id.class)) {
                name = field.getName();
                break;
            }
        }
        if (name != null) {
            return name;
        } else {
            throw new NoPrimaryKey("the primary key is undefined");
        }
    }

    /**
     * 获取字段名
     *
     * @param method 方法名
     * @return 字段数组
     */
    public static String[] getFields(Thread thread, Method[] methods, MySQL mySQL) {
        Method m = getMethod(thread, methods);
        Objects.requireNonNull(m);
        String[] split = m.getName().split("By");
        if (split.length != 2) {
            throw new FieldException(m.getName() + " name is not standard.");
        }
        switch (mySQL) {
            case AND:
                split = split[1].split("And");
                break;
            case OR:
                split = split[1].split("Or");
                break;
        }
        String[] res = new String[split.length];
        for (int i = 0; i < split.length; i++) {
            split[i] = split[i].substring(0, 1).toLowerCase(Locale.ROOT) + split[i].substring(1);
            res[i] = removeHump(split[i]);
        }
        if (res.length != 2) {
            throw new FieldException(m.getName() + " name is not standard.");
        }
        return res;
    }

    /**
     * SQL预编译
     *
     * @param fields
     * @param obj
     * @param ps
     * @param i
     * @throws IllegalAccessException
     * @throws SQLException
     */
    public static void doSetValue(Field[] fields, Object obj, PreparedStatement ps, int i) throws IllegalAccessException, SQLException {
        fields[i].setAccessible(true); //打破私有属性的访问权限
        Object value = fields[i].get(obj);
        value = fields[i].isAnnotationPresent(MD5.class) ? code(value.toString(), fields[i].getAnnotation(MD5.class).value()) : value;
        isNull(ps, i, value);
    }

    /**
     * SQL预编译
     *
     * @param field
     * @param obj
     * @param ps
     * @param i
     * @throws IllegalAccessException
     * @throws SQLException
     */
    public static void doSetValue(Field field, Object obj, PreparedStatement ps, int i) throws IllegalAccessException, SQLException {
        field.setAccessible(true); //打破私有属性的访问权限
        Object value = field.get(obj);
        value = field.isAnnotationPresent(MD5.class) ? code(value.toString(), field.getAnnotation(MD5.class).value()) : value;
        isNull(ps, i, value);
    }

    /**
     * 判断是否为空
     *
     * @param ps
     * @param i
     * @param value
     * @throws SQLException
     */
    private static void isNull(PreparedStatement ps, int i, Object value) throws SQLException {
        if (value != null) {
            if (value instanceof Date) {
                Date date = (Date) value;
                ps.setTimestamp(i, new Timestamp(date.getTime()));
            } else {
                if (Typeof.CHARACTER.value().equals(value.getClass().getName())) {
                    ps.setString(i, String.valueOf(value));
                } else {
                    ps.setObject(i, value);
                }
            }
        } else {
            ps.setObject(i, null);
        }
    }

    /**
     * 获取字段名
     *
     * @param thread 线程
     * @return 字段数组
     */
    public static String getField(Thread thread, Method[] methods) {
        Method m = getMethod(thread, methods);
        Objects.requireNonNull(m);
        String[] split = m.getName().split("By");
        if (split.length != 2) {
            throw new FieldException(m.getName() + " name is not standard.");
        }
        return removeHump(split[1].substring(0, 1).toLowerCase(Locale.ROOT) + split[1].substring(1));
    }

    /**
     * 编译SQL
     *
     * @param objects 编译参数
     * @return SQL
     */
    public static String compileSQL(Object... objects) {
        StringBuilder sql = new StringBuilder();
        for (Object object : objects) {
            if (object.equals($)) {
                sql.append("* ");
            } else if (object.equals(eq)) {
                sql.append("= ");
            } else if (object.equals(gt)) {
                sql.append("> ");
            } else if (object.equals(gts)) {
                sql.append(">= ");
            } else if (object.equals(lt)) {
                sql.append("< ");
            } else if (object.equals(lts)) {
                sql.append("<= ");
            } else if (object instanceof BuildSQL) {
                sql.append(((BuildSQL) object).getName()).append(" ");
            } else if (object instanceof List) {
                List<BuildSQL> res = (List<BuildSQL>) object;
                for (BuildSQL re : res) {
                    sql.append(re.getName()).append(" ");
                }
            } else {
                sql.append(object).append(" ");
            }
        }
        return sql.toString().replace(", " + FROM, " " + FROM).trim();
    }


    /**
     * 获得数据类型
     *
     * @param val
     * @return
     */
    private static String fieldType(String field, String val, String len) {
        String type = "";
        Typeof typeof = Objects.requireNonNull(MatchType.get(val));
        switch (typeof) {
            case $INT:
            case INT:
                type = addLen(field, len, MySQL.INT, "%s %s(11)");
                break;
            case STRING:
                type = addLen(field, len, MySQL.VARCHAR, "%s %s(255)");
                break;
            case $DOUBLE:
            case DOUBLE:
                type = String.format("%s %s", field, MySQL.DOUBLE);
                break;
            case $FLOAT:
            case FLOAT:
                type = String.format("%s %s", field, FLOAT);
                break;
            case DATE:
                type = String.format("%s %s", field, MySQL.TIMESTAMP);
                break;
            case $BOOLEAN:
            case BOOLEAN:
                type = String.format("%s %s(1)", field, MySQL.BIT);
                break;
            case $SHORT:
            case SHORT:
                type = String.format("%s %s(6)", field, MySQL.SMALLINT);
                break;
            case $BYTE:
            case BYTE:
                type = String.format("%s %s(4)", field, MySQL.TINYINT);
                break;
            case $LONG:
            case LONG:
                type = String.format("%s %s(20)", field, MySQL.BIGINT);
                break;
            case CHAR:
                type = String.format("%s %s(1)", field, MySQL.CHAR);
                break;
        }
        return type;
    }

    /**
     * 包装数据类型
     *
     * @param entity 包装对象
     * @param rs     结果集
     * @param field  字段
     * @throws SQLException sql异常
     */
    public static void buildField(Object entity, ResultSet rs, Field field) throws SQLException {
        if (!field.isAnnotationPresent(IgnoreField.class)) {
            String name = field.getType().getName();
            Typeof typeof = Objects.requireNonNull(MatchType.get(name));
            String humpName = StringUtil.removeHump(field.getName());
            switch (typeof) {
                case CHAR:
                    Executor.doSetFieldValue(rs.getObject(humpName), () -> field.setChar(entity, rs.getString(humpName).charAt(0)));
                    break;
                case $SHORT:
                    Executor.doSetFieldValue(rs.getObject(humpName), () -> field.setShort(entity, rs.getShort(humpName)));
                    break;
                case SHORT:
                    Executor.doSetFieldValue(rs.getObject(humpName), () -> field.set(entity, rs.getShort(humpName)));
                    break;
                case $BYTE:
                    Executor.doSetFieldValue(rs.getObject(humpName), () -> field.setByte(entity, rs.getByte(humpName)));
                    break;
                case BYTE:
                    Executor.doSetFieldValue(rs.getObject(humpName), () -> field.set(entity, rs.getByte(humpName)));
                    break;
                default:
                    Executor.doSetFieldValue(rs.getObject(humpName), () -> field.set(entity, rs.getObject(humpName)));
                    break;
            }
        }
    }

    private static String addLen(String field, String len, MySQL anInt, String s) {
        String type;
        if (len.length() != 0) {
            type = String.format("%s %s(%s)", field, anInt, len);
        } else {
            type = String.format(s, field, anInt);
        }
        return type;
    }

    /**
     * 获得建库语句
     *
     * @param fields    字段数组
     * @param tableName 表名
     * @return sql语句
     */
    public static String getTableSql(Field[] fields, String tableName) {
        String sql = String.format("%s %s %s %s %s %s (", MySQL.CREATE, MySQL.TABLE, MySQL.IF, MySQL.NOT, MySQL.EXISTS, tableName);
        StringBuilder mid = new StringBuilder();
        String key = "id";
        for (Field field : fields) {
            key = getFieldSql(mid, key, field);
        }
        sql = String.format("%s%s %s %s_pk %s %s (%s));", sql, mid, MySQL.CONSTRAINT, tableName, MySQL.PRIMARY, MySQL.KEY, key);
        return sql;
    }

    /**
     * 通过字段获取Sql
     *
     * @param mid   sql存储器
     * @param key   id关键字
     * @param field 字段
     * @return sql
     */
    public static String getFieldSql(StringBuilder mid, String key, Field field) {
        field.setAccessible(true);
        String len = field.isAnnotationPresent(Len.class) ? field.getDeclaredAnnotation(Len.class).value() : "";
        boolean isNull = field.isAnnotationPresent(FNull.class);
        boolean Id = field.isAnnotationPresent(Id.class);
        boolean ignoreF = field.isAnnotationPresent(IgnoreField.class);
        String name = removeHump(field.getName()).trim();
        if (!ignoreF) {
            String s = fieldType(name, field.getType().getName().trim(), len);
            if (Id) {
                IdType idType = field.getDeclaredAnnotation(Id.class).strategy();
                //type = String.format("%s %s(20) %s", field, MySQL.BIGINT, MySQL.AUTO_INCREMENT);
                if (idType == IdType.AUTO) {
                    mid.append(s).append(" ").append(AUTO_INCREMENT).append(",");
                } else {
                    mid.append(s).append(",");
                }
            } else if (field.isAnnotationPresent(Lob.class)) {
                String type = isNull ? String.format("%s %s %s", name, MySQL.LONGTEXT, MySQL.NULL) : String.format("%s %s %s %s", name, MySQL.LONGTEXT, MySQL.NOT, MySQL.NULL);
                mid.append(type).append(",");
            } else if (field.isAnnotationPresent(Character.class)) {
                // `content` longtext character SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
                String code = field.getDeclaredAnnotation(Character.class).encode().get();
                s = isNull ? String.format("%s %s %s %s %s %s_bin %s", s, CHARACTER, SET, code, COLLATE, code, NULL) : String.format("%s %s %s %s %s %s_bin %s %s", s, CHARACTER, SET, code, COLLATE, code, NOT, NULL);
                mid.append(s).append(",");
            } else {
                s = isNull ? String.format(" %s %s", s, NULL) : String.format(" %s %s %s", s, NOT, NULL);
                mid.append(s).append(",");
            }
        }
        if (Id) {
            key = removeHump(field.getName());
        }
        return key;
    }

    /**
     * 通过堆栈和注解获得SQL语句
     *
     * @param element
     * @return
     */
    public static String getSQLByStackTraceElement(Thread element, Method[] methods) {
        String sql = "";
        Method m = getMethod(element, methods);
        Objects.requireNonNull(m);
        if (m.isAnnotationPresent(Query.class)) {
            sql = m.getDeclaredAnnotation(Query.class).value();
        } else {
            throw new NoSqlException("此方法没有 @SQL注解.");
        }
        return sql;
    }

    /**
     * 通过堆栈和注解获得方法
     *
     * @param element
     * @return
     */
    public static Method getMethod(Thread element, Method[] methods) {
        Method m = null;
        for (Method method : methods) {
            if (Arrays.stream(element.getStackTrace()).filter(e -> !e.isNativeMethod()).anyMatch(el -> el.getMethodName().equals(method.getName()))) {
                return method;
            }
        }
        return null;
    }

    public static Class<?>[] getSqlFieldClass(String nString) {
        Class<?>[] clazz = new Class[2];
        String[] split = nString.trim().split("\\(");
        String name = split[0];
        name = name.toUpperCase(Locale.ROOT);
        if (name.equals(TIMESTAMP.toString())) {
            clazz[0] = Date.class;
        } else if (name.equals(LONGTEXT.toString())) {
            clazz[0] = String.class;
        } else if (name.equals(VARCHAR.toString())) {
            clazz[0] = String.class;
        } else if (name.equals(CHAR.toString())) {
            clazz[0] = char.class;
        } else if (name.equals(INT.toString())) {
            clazz[0] = int.class;
            clazz[1] = Integer.class;
        } else if (name.equals(TINYINT.toString())) {
            clazz[0] = byte.class;
            clazz[1] = Byte.class;
        } else if (name.equals(SMALLINT.toString())) {
            clazz[0] = short.class;
            clazz[1] = Short.class;
        } else if (name.equals(BIGINT.toString())) {
            clazz[0] = long.class;
            clazz[1] = Long.class;
        } else if (name.equals(BIT.toString())) {
            clazz[0] = boolean.class;
            clazz[1] = Boolean.class;
        } else if (name.equals(DOUBLE.toString())) {
            clazz[0] = double.class;
            clazz[1] = Double.class;
        } else if (name.equals(FLOAT.toString())) {
            clazz[0] = float.class;
            clazz[1] = Float.class;
        }
        return clazz;
    }
}
